﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;


namespace DAO
{
    public class BCDTTheoMatDoSuDungPhongDAO
    {
        public class SqlConnectionData
        {
            public static SqlConnection Connection()
            {
                SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=QLKS;Integrated Security=True");
                return conn;
            }
        }
        //public static void InsertBCDTTheoMatDoSuDungPhong(BCDTTheoMatDoSuDungPhongDTO bcmd)
        //{
        //    SqlConnection conn = SqlConnectionData.Connection();
        //    SqlCommand cmd = new SqlCommand("sp_InsertLoaiKhach", conn);
        //    cmd.CommandType = CommandType.StoredProcedure;
        //    cmd.Parameters.Add("@ThangBaoCao", SqlDbType.DateTime);
        //    cmd.Parameters.Add("@TongNgayThue", SqlDbType.Int);
        //    cmd.Parameters["@ThangBaoCao"].Value = bcmd.ThangBaoCao;
        //    cmd.Parameters["@TongNgayThue"].Value = bcmd.TongNgayThue;
        //    conn.Open();
        //    cmd.ExecuteNonQuery();
        //    conn.Close();
        //}
        public static DataTable LayBaoCaoDoanhThuTheomatDo(int thangBaoCao, int namBaoCao)
        {
            string ngayDauThang = namBaoCao.ToString() + "-" + thangBaoCao.ToString() + "-" + "01";
            string ngay = null;
            switch (thangBaoCao)
            {
                case 1:
                case 3:
                case 5:
                case 7:
                case 8:
                case 10:
                case 12:
                    ngay = "31";
                    break;
                case 4:
                case 6:
                case 9:
                case 11:
                    ngay = "30";
                    break;
                case 2:
                    if (namBaoCao % 4 == 0)
                    {
                        if (namBaoCao % 100 == 0)
                        {
                            if (namBaoCao % 400 == 0)
                            {
                                ngay = "29";
                            }
                            else
                            {
                                ngay = "28";
                            }
                        }
                        else
                        {
                            ngay = "29";
                        }
                    }
                    else
                    {
                        ngay = "28";
                    }
                    break;
            }
            string ngayCuoiThang = namBaoCao.ToString() + "-" + thangBaoCao.ToString() + "-" + ngay;
            string sql = "Select * From BCDTTHEOMATDOSUDUNGPHONG Where ThangBaoCao between '" + ngayDauThang + "' and '" + ngayCuoiThang + "'";
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }

        public static Boolean soSanhThangBaoCaoTheoMatDo(int thangBaoCao)
        {
            Boolean kq = true;
            string sql = "Select * From BCDTTHEOMATDOSUDUNGPHONG Where Month(ThangBaoCao)=" + thangBaoCao;
            DataTable dt = DataProvider.ExecuteQuery(sql);
            if (dt.Rows.Count > 0)
            {
                kq = false;
            }
            return kq;
        }

        public static DataTable GetAll()
        {
            string sql = "Select * From BCDTTHEOMATDOSUDUNGPHONG";
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }

        public static void InsertBCDTTheoMatDoSuDungPhong(BCDTTheoMatDoSuDungPhongDTO md)
        {
            string sql = string.Format("INSERT INTO BCDTTHEOMATDOSUDUNGPHONG (ThangBaoCao, TongNgayThue) VALUES ('" + md.ThangBaoCao + "', " + md.TongNgayThue + ")");
            DataProvider.ExecuteNonQuery(sql);
        }
        public static DataTable LayMaxMaBCDTTheoMatDoSuDungPhong()
        {
            string sql = "select MAX(MaBCMD) from BCDTTHEOMATDOSUDUNGPHONG";
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }
        
    }
}
